import numpy as np
import pymysql
import pandas as pd
import pymysql.cursors

class MysqulUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        passwd='root',
        database='scenic',
        port=3306,
        charset='utf8'
    )
        
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT o.tourist_agency_name,u.id_no,LEFT(u.id_no,2) as province_code,
        DAYOFWEEK(gate.create_time) as non_weekend,
        cast(substring(u.id_no,7,4) as unsigned) as birth_year
        FROM ticket_order_user_rel u JOIN ticket_order o on u.order_id = o.id JOIN order_user_gate_rel gate on gate.ticket_rel_id = u.id
        WHERE o.tourist_agency_name != '' and o.pay_time !=''
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        #print(df)
        
        df['non_weekend'] = df['non_weekend'].apply(lambda x:1 if x not in [1,7] else 0)
        
        #新增有效性标记列
        df['valid_id'] = df['id_no'].apply(lambda x : 1 if x and str(x).strip() != '' else 0)
        
        #标记是否老年人游客及外省游客
        df['elderly_ratio'] = df.apply(
            lambda x : 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        df['out_province_ratio'] = df.apply(
            lambda x : 1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        
        #分组聚合计算占比
        result = df.groupby(['tourist_agency_name']).agg(
            total_visitors=('id_no','count'), #总游客数
            valid_visitors=('valid_id','sum'), #有效身份证游客数
            out_province=('out_province_ratio','sum'), #外省人数（仅有效id_no）
            elderly=('elderly_ratio','sum'), #老年人人数（仅有效id_no）
            non_weekend_ratio=('non_weekend','mean'), #非周末游客占比（仅有效id_no）
        ).reset_index()
        
        #计算实际比例
        result['out_province_ratio'] = result['out_province'] / result['valid_visitors'].replace(0,np.nan)
        result['elderly_ratio'] = result['elderly'] / result['valid_visitors'].replace(0,np.nan)
        
        #清除中间列
        result =result.drop(['out_province','elderly'], axis=1)
        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
        result['elderly_ratio'] = result['elderly_ratio'].fillna(0)
        print(result.head)
        result.to_csv('scenic_data.csv')
              
if __name__ == '__main__':
    mu = MysqulUtils()
    mu.get_data()